package com.hnkywl.sts.dao.cwgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cwgl.Qtpz;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.axis2.jaxws.description.xml.handler.PropertyType;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.*;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: lenovo
 * Date: 13-5-18
 * Time: 下午12:53
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class QtpzDao extends SimpleHibernateTemplate<Qtpz, Long> {
    /**
     * 接口操作有三个按钮：导入，重置，删除
     * 接口操作有三个按钮对应状态：导入（未导入状态），重置（导入失败、删除失败），删除（导入成功）
     * 分以下几种情况：
     * insertFlag=1 and deleteFlag=1 显示"导入"
     * insertFlag=2 and deleteFlag=1 显示"重置"
     * insertFlag=3 and deleteFlag=1 显示"删除"
     * insertFlag=3 and deleteFlag=2 显示"重置"
     * insertFlag=1 and deleteFlag=3 显示"导入"
     * insertFlag=2 and deleteFlag=3 显示"重置"
     * 以下三种情况不存在
     * insertFlag=2 and deleteFlag=2
     * insertFlag=3 and deleteFlag=3
     * insertFlag=1 and deleteFlag=2
     *
     * @param status
     * @param saveStatus
     * @param startDate
     * @param endDate
     * @param companyId
     * @param no
     * @param voucherNum
     * @param createUser
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Qtpz> findQtpz(Integer status, Integer saveStatus, Date startDate, Date endDate, Long companyId, Long no, Integer voucherNum,String word, String createUser, Integer pageNo, Integer pageSize) {
        Page<Qtpz> page = new Page<Qtpz>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
//                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .createAlias("nbgs", "nbgs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Projections.sqlProjection("{alias}.id as id", new String[]{"id"}, new Type[]{Hibernate.LONG}))
                        .add(Projections.sqlProjection("{alias}.zdrq as zdrq", new String[]{"zdrq"}, new Type[]{Hibernate.DATE}))
                        .add(Projections.sqlProjection("{alias}.date_created as dateCreated", new String[]{"dateCreated"}, new Type[]{Hibernate.DATE}))
                        .add(Property.forName("nbgs.qc").as("nbgsqc"))
                        .add(Property.forName("createUser.xm").as("xm"))
                        .add(Property.forName("zh").as("zh"))
                        .add(Property.forName("zy").as("zy"))
                        .add(Projections.sqlProjection("{alias}.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.INTEGER}))
                        .add(Projections.sqlProjection("{alias}.jiuqi_voucher_num as voucherNum", new String[]{"voucherNum"}, new Type[]{Hibernate.INTEGER}))
                        .add(Property.forName("bz").as("bz"))
                        .add(Projections.sqlProjection(
                                "(case when {alias}.jiuqi_insert_flag=1 and {alias}.jiuqi_delete_flag=1 then 'wdr' " + //未导入
                                        "when {alias}.jiuqi_insert_flag=1 and {alias}.jiuqi_delete_flag=3 then 'sccg' " + //删除成功
                                        "when {alias}.jiuqi_insert_flag=3 and {alias}.jiuqi_delete_flag=1 then 'drcg' " +//导入成功
                                        "when ({alias}.jiuqi_insert_flag=2 and {alias}.jiuqi_delete_flag=1) or ({alias}.jiuqi_insert_flag=2 and {alias}.jiuqi_delete_flag=3) then 'drsb' " + //导入失败
                                        "when ({alias}.jiuqi_insert_flag=3 and {alias}.jiuqi_delete_flag=2) then 'scsb' " +  //删除失败
                                        "else 'none' end) as czzt",
                                new String[]{"czzt"}, new Type[]{Hibernate.STRING})))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (status != null) {
            //status=0未导入
            //status=1导入成功
            //status=2导入失败
            //status=3删除成功
            //status=4删除失败
            criteria.add(Restrictions.sqlRestriction(
                    "((" + status + "=0 and {alias}.jiuqi_insert_flag=1) " +
                            "or (" + status + "=1 and {alias}.jiuqi_insert_flag=3 and {alias}.jiuqi_delete_flag!=2) " +
                            "or (" + status + "=2 and {alias}.jiuqi_insert_flag=2) " +
                            "or (" + status + "=3 and {alias}.jiuqi_delete_flag=3 and {alias}.jiuqi_insert_flag!=2) " +
                            "or (" + status + "=4 and {alias}.jiuqi_delete_flag=2)) "));
        }
        if (saveStatus != null) {
            criteria.add(Property.forName("zt").eq(saveStatus));
        }
        if (startDate != null) {
            criteria.add(Property.forName("zdrq").ge(startDate));
        }
        if (endDate != null) {
            criteria.add(Property.forName("zdrq").lt(endDate));
        }
        if (companyId != null) {
            criteria.add(Property.forName("nbgs.id").eq(companyId));
        }
        if (no != null) {
            criteria.add(Property.forName("id").eq(no));
        }
        if (voucherNum != null) {
            criteria.add(Property.forName("voucherNum").eq(voucherNum));
        }
        if (StringUtils.isNotEmpty(createUser)) {
            criteria.add(Property.forName("createUser.xm").like(createUser, MatchMode.ANYWHERE));
        }
        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }
}
